import sqlite3
from datetime import datetime
# CREATE TABLE Books (
#     book_id INT PRIMARY KEY AUTO_INCREMENT,
#     title VARCHAR(255) NOT NULL,
#     author VARCHAR(255),
#     isbn VARCHAR(20) UNIQUE,
#     quantity INT,
#     available_quantity INT
# );
# CREATE TABLE Members (
#     member_id INT PRIMARY KEY AUTO_INCREMENT,
#     name VARCHAR(255),
#     email VARCHAR(255) UNIQUE,
#     registration_date DATE
# );
# CREATE TABLE BorrowingRecords (
#     record_id INT PRIMARY KEY AUTO_INCREMENT,
#     member_id INT,
#     book_id INT,
#     borrow_date DATE,
#     return_date DATE,
#     due_date DATE,
#     FOREIGN KEY (member_id) REFERENCES Members(member_id),
#     FOREIGN KEY (book_id) REFERENCES Books(book_id)
# );

# 数据库类：用于连接和操作数据库
# class Database:
#     def __init__(self, db_name="library.db"):
#         self.conn = sqlite3.connect(db_name)
#         self.cursor = self.conn.cursor()
#         self.create_tables()
#     # 创建数据库表
#     def create_tables(self):
#         self.cursor.execute('''CREATE TABLE IF NOT EXISTS Books (
#                                 book_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                                 title TEXT NOT NULL,
#                                 author TEXT,
#                                 isbn TEXT UNIQUE,
#                                 quantity INTEGER,
#                                 available_quantity INTEGER)''')
#         self.cursor.execute('''CREATE TABLE IF NOT EXISTS Members (
#                                 member_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                                 name TEXT NOT NULL,
#                                 email TEXT UNIQUE,
#                                 registration_date TEXT)''')
#         self.cursor.execute('''CREATE TABLE IF NOT EXISTS BorrowingRecords (
#                                 record_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                                 member_id INTEGER,
#                                 book_id INTEGER,
#                                 borrow_date TEXT,
#                                 return_date TEXT,
#                                 due_date TEXT,
#                                 FOREIGN KEY (member_id) REFERENCES Members(member_id),
#                                 FOREIGN KEY (book_id) REFERENCES Books(book_id))''')
#         self.conn.commit()
#     # 关闭数据库连接
#     def close(self):
#         self.conn.close()
#     # 查询图书
#     def query_book(self, book_id):
#         self.cursor.execute("SELECT * FROM Books WHERE book_id=?", (book_id,))
#         return self.cursor.fetchone()
#     # 添加图书
#     def add_book(self, title, author, isbn, quantity):
#         self.cursor.execute("INSERT INTO Books (title, author, isbn, quantity, available_quantity) VALUES (?, ?, ?, ?, ?)",
#                             (title, author, isbn, quantity, quantity))
#         self.conn.commit()
#     # 查询会员
#     def query_member(self, member_id):
#         self.cursor.execute("SELECT * FROM Members WHERE member_id=?", (member_id,))
#         return self.cursor.fetchone()
#     # 添加会员
#     def add_member(self, name, email):
#         registration_date = datetime.now().strftime("%Y-%m-%d")
#         self.cursor.execute("INSERT INTO Members (name, email, registration_date) VALUES (?, ?, ?)",
#                             (name, email, registration_date))
#         self.conn.commit()
#     # 借书记录
#     def borrow_book(self, member_id, book_id):
#         book = self.query_book(book_id)
#         if book and book[5] > 0:  # available_quantity > 0
#             borrow_date = datetime.now().strftime("%Y-%m-%d")
#             due_date = (datetime.now() + timedelta(days=14)).strftime("%Y-%m-%d")  # 14 days loan period
#             self.cursor.execute("INSERT INTO BorrowingRecords (member_id, book_id, borrow_date, due_date) VALUES (?, ?, ?, ?)",
#                                 (member_id, book_id, borrow_date, due_date))
#             self.cursor.execute("UPDATE Books SET available_quantity = available_quantity - 1 WHERE book_id = ?", (book_id,))
#             self.conn.commit()
#             print(f"Book '{book[1]}' has been borrowed successfully.")
#         else:
#             print("Book is not available for borrowing.")
#     # 还书记录
#     def return_book(self, member_id, book_id):
#         self.cursor.execute("SELECT * FROM BorrowingRecords WHERE member_id=? AND book_id=? AND return_date IS NULL", (member_id, book_id))
#         record = self.cursor.fetchone()
#         if record:
#             return_date = datetime.now().strftime("%Y-%m-%d")
#             self.cursor.execute("UPDATE BorrowingRecords SET return_date=? WHERE record_id=?", (return_date, record[0]))
#             self.cursor.execute("UPDATE Books SET available_quantity = available_quantity + 1 WHERE book_id = ?", (book_id,))
#             self.conn.commit()
#             print(f"Book '{book_id}' returned successfully.")
#         else:
#             print("No record found for this book.")
# # 会员类
# class Member:
#     def __init__(self, db, member_id, name, email):
#         self.db = db
#         self.member_id = member_id
#         self.name = name
#         self.email = email
#     # 借书方法
#     def borrow_book(self, book_id):
#         self.db.borrow_book(self.member_id, book_id)
#     # 还书方法
#     def return_book(self, book_id):
#         self.db.return_book(self.member_id, book_id)
# # 图书类
# class Book:
#     def __init__(self, db, book_id, title, author, isbn, quantity):
#         self.db = db
#         self.book_id = book_id
#         self.title = title
#         self.author = author
#         self.isbn = isbn
#         self.quantity = quantity
#     # 查询图书信息
#     def get_info(self):
#         return self.db.query_book(self.book_id)
# # 测试系统
# def test_system():
#     db = Database()
#     # 添加图书
#     db.add_book("Python程序设计", "张三", "123456789", 5)
#     db.add_book("软件工程", "李四", "987654321", 3)
#     # 添加会员
#     db.add_member("王五", "alice@example.com")
#     db.add_member("李四", "bob@example.com")
#     # 创建会员对象
#     member1 = Member(db, 1, "Alice", "alice@example.com")
#     member2 = Member(db, 2, "Bob", "bob@example.com")
#     # 创建图书对象
#     book1 = Book(db, 1, "Python Programming", "John Doe", "123456789", 5)
#     book2 = Book(db, 2, "Data Structures", "Jane Smith", "987654321", 3)
#     # Alice借书
#     member1.borrow_book(1)
#     member1.borrow_book(2)
#     # Bob尝试借书
#     member2.borrow_book(1)  # Bob尝试借Alice已经借走的图书
#     # Alice还书
#     member1.return_book(1)
#     db.close()
# if __name__ == "__main__":
#     test_system()


